﻿using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations;
namespace Chap23_Sql
{
    internal class Program
    {
        #region 成功的代码
        //static async Task Main()
        //{
        //    using (var db = new BookContext())
        //    {
        //        // 创建数据库（如果不存在）
        //        await db.Database.EnsureCreatedAsync();

        //        // 插入种子数据到 Books 表中
        //        if (!await db.Books.AnyAsync())
        //        {
        //            Book book1 = new Book
        //            {
        //                Title = "Beginning C# 7",
        //                Author = "Perkins, Reid, and Hammer"
        //            };
        //            Book book2 = new Book
        //            {
        //                Title = "Beginning XML",
        //                Author = "Fawcett, Quin, and Ayers"
        //            };
        //            db.Books.Add(book1);
        //            db.Books.Add(book2);

        //            await db.SaveChangesAsync();
        //        }

        //        // 插入种子数据到 Stores 和 Stocks 表中
        //        if (!await db.Stores.AnyAsync())
        //        {
        //            // 获取已经存在的 Book 对象
        //            var existingBooks = await db.Books.ToListAsync();
        //            var book1 = existingBooks.FirstOrDefault(b => b.Title == "Beginning C# 7");
        //            var book2 = existingBooks.FirstOrDefault(b => b.Title == "Beginning XML");

        //            var store1 = new Store
        //            {
        //                Name = "Main St Books",
        //                Address = "123 Main St",
        //                Inventory = new List<Stock>()
        //            };
        //            db.Stores.Add(store1);

        //            Stock store1book1 = new Stock
        //            { Item = book1, OnHand = 4, OnOrder = 6 };
        //            store1.Inventory.Add(store1book1);

        //            Stock store1book2 = new Stock
        //            { Item = book2, OnHand = 1, OnOrder = 9 };
        //            store1.Inventory.Add(store1book2);

        //            var store2 = new Store
        //            {
        //                Name = "Campus Books",
        //                Address = "321 College Ave",
        //                Inventory = new List<Stock>()
        //            };

        //            db.Stores.Add(store2);

        //            Stock store2book1 = new Stock
        //            { Item = book1, OnHand = 7, OnOrder = 23 };
        //            store2.Inventory.Add(store2book1);

        //            Stock store2book2 = new Stock
        //            { Item = book2, OnHand = 2, OnOrder = 8 };
        //            store2.Inventory.Add(store2book2);


        //            await db.SaveChangesAsync();
        //        }

        //        // 查询数据，使用 Include 方法预加载 Inventory 数据
        //        var query1 = from store in db.Stores.Include(s => s.Inventory).ThenInclude(i => i.Item)
        //                     orderby store.Name
        //                     select store;

        //        Console.WriteLine("Bookstore Inventory Report:");
        //        Console.WriteLine();
        //        foreach (var store in query1)
        //        {
        //            Console.WriteLine($"{store.Name} located at {store.Address}");

        //            foreach (Stock stock in store.Inventory)
        //            {
        //                Console.WriteLine($"- Title: {stock.Item.Title}");
        //                Console.WriteLine($"-- Copies in Store: {stock.OnHand}");
        //                Console.WriteLine($"-- Copies on Order: {stock.OnOrder}");
        //            }
        //            Console.WriteLine();
        //        }

        //        Console.WriteLine("Press a key to exit...");
        //        Console.ReadKey();
        //    }
        //}
        #endregion

        static async Task Main()
        {
            using (var db = new BookContext())
            {
                // 创建数据库（如果不存在）
                await db.Database.EnsureCreatedAsync();

                // 插入种子数据到 Stores 和 Stocks 表中
                if (!await db.Stores.AnyAsync())
                {
                    Book book1 = new Book
                    {
                        Title = "Beginning C# 7",
                        Author = "Perkins, Reid, and Hammer"
                    };
                    Book book2 = new Book
                    {
                        Title = "Beginning XML",
                        Author = "Fawcett, Quin, and Ayers"
                    };
                    db.Books.Add(book1);
                    db.Books.Add(book2);
                    

                    var store1 = new Store
                    {
                        Name = "Main St Books",
                        Address = "123 Main St",
                        Inventory = new List<Stock>()
                    };
                    db.Stores.Add(store1);

                    Stock store1book1 = new Stock
                    { Item = book1, OnHand = 4, OnOrder = 6 };
                    store1.Inventory.Add(store1book1);

                    Stock store1book2 = new Stock
                    { Item = book2, OnHand = 1, OnOrder = 9 };
                    store1.Inventory.Add(store1book2);

                    var store2 = new Store
                    {
                        Name = "Campus Books",
                        Address = "321 College Ave",
                        Inventory = new List<Stock>()
                    };

                    db.Stores.Add(store2);

                    Stock store2book1 = new Stock
                    { Item = book1, OnHand = 7, OnOrder = 23 };
                    store2.Inventory.Add(store2book1);

                    Stock store2book2 = new Stock
                    { Item = book2, OnHand = 2, OnOrder = 8 };
                    store2.Inventory.Add(store2book2);


                    await db.SaveChangesAsync();
                }

                // 查询数据，使用 Include 方法预加载 Inventory 数据
                var query1 = from store in db.Stores.Include(s => s.Inventory).ThenInclude(i => i.Item)
                             orderby store.Name
                             select store;

                Console.WriteLine("Bookstore Inventory Report:");
                Console.WriteLine();
                foreach (var store in query1)
                {
                    Console.WriteLine($"{store.Name} located at {store.Address}");

                    foreach (Stock stock in store.Inventory)
                    {
                        Console.WriteLine($"- Title: {stock.Item.Title}");
                        Console.WriteLine($"-- Copies in Store: {stock.OnHand}");
                        Console.WriteLine($"-- Copies on Order: {stock.OnOrder}");
                    }
                    Console.WriteLine();
                }

                Console.WriteLine("Press a key to exit...");
                Console.ReadKey();
            }
        }
    }

    public class Book
    {
        public string Title { get; set; }
        public string Author { get; set; }
        [Key] public int Code { get; set; }
    }

    /// <summary>
    /// 数据库上下文   用来管理、创建、更新、删除数据库中的数据
    /// </summary>
    public class BookContext : DbContext
    {
        public DbSet<Book> Books { get; set; }
        public DbSet<Store> Stores { get; set; }
        public DbSet<Stock> Stocks { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                // 请根据实际情况修改连接字符串
                optionsBuilder.UseSqlServer("Data Source=localhost;Initial Catalog=BeginningCSharp7_23_1_CodeFirstDatabase.BookContext;Integrated Security=True;Trust Server Certificate=True");
            }
        }
    }
}
